unit unitmainform;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqldb, sqlite3conn, db, pqconnection, FileUtil, Forms,
  Controls, Graphics, Dialogs, DBGrids, Menus;

type

  { TMainForm }

  TMainForm = class(TForm)
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    MainMenu1: TMainMenu;
    MenuItem1: TMenuItem;
    MenuItem2: TMenuItem;
    MenuItem3: TMenuItem;
    MenuItem4: TMenuItem;
    MenuItem5: TMenuItem;
    MenuItem6: TMenuItem;
    MenuItem7: TMenuItem;
    PQConnection1: TPQConnection;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLQuery2: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    SQLTransaction2: TSQLTransaction;
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure MenuItem2Click(Sender: TObject);
    procedure MenuItem4Click(Sender: TObject);
    procedure MenuItem6Click(Sender: TObject);
    procedure MenuItem7Click(Sender: TObject);
    procedure ordenar_grid();
  private
    prd_cdg : string;
    prd_nombre : string;
    prd_descripcion : string;
    prd_precio1 : string;
    prd_precio2 : string;
    prd_precio3 : string;
    prd_precio4 : string;
    nombre : string;
  public
    { public declarations }
  end;

type
  config = record
    codigo_sucursal : string[3];
    host : string[50];
    db : string[20];
    user : string[20];
    passwd : string[20];
    ip_impresor : string[15];
    tipo_impresor : string[20];
  end;

var
  MainForm: TMainForm;
  archivo : file of config;
  registro : config;

implementation

{$R *.lfm}

{ TMainForm }

procedure TMainForm.ordenar_grid();
var
  QueryString : string;

begin
  If SQLQuery2.Active = true then
    SQLQuery2.Close;

  QueryString := 'select * from producto where prd_nombre like ''%' + nombre
    + '%'' order by prd_nombre';

  SQLQuery2.SQL.Text := QueryString;

  SQLQuery2.Open;
  DBGrid1.Columns[0].Title.Caption := 'Código';
  DBGrid1.Columns[1].Title.Caption := 'Nombre';
  DBGrid1.Columns[2].Title.Caption := 'Descripcion';
  DBGrid1.Columns[3].Title.Caption := 'Precio1';
  DBGrid1.Columns[4].Title.Caption := 'Precio2';
  DBGrid1.Columns[5].Title.Caption := 'Precio3';
  DBGrid1.Columns[6].Title.Caption := 'Precio4';

  DBGrid1.Columns[0].Width := 100;
  DBGrid1.Columns[1].Width := 310;
  DBGrid1.Columns[2].Width := 320;
  DBGrid1.Columns[3].Width := 80;
  DBGrid1.Columns[4].Width := 80;
  DBGrid1.Columns[5].Width := 80;
  DBGrid1.Columns[6].Width := 80;

  TColumn(DBGrid1.Columns[3]).DisplayFormat := '$ ####0.00';
  TColumn(DBGrid1.Columns[4]).DisplayFormat := '$ ####0.00';
  TColumn(DBGrid1.Columns[5]).DisplayFormat := '$ ####0.00';
  TColumn(DBGrid1.Columns[6]).DisplayFormat := '$ ####0.00';
end;

procedure TMainForm.FormShow(Sender: TObject);
begin
  nombre := '%';
  ordenar_grid();
end;

procedure TMainForm.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  SQLQuery2.ApplyUpdates;
  SQLTransaction2.Commit;
  SQLite3Connection1.Close;
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
  SQLite3Connection1.Connected := true;
end;

procedure TMainForm.MenuItem2Click(Sender: TObject);
begin
  Close;
end;

procedure TMainForm.MenuItem4Click(Sender: TObject);
var
  QueryString : String;

begin
  AssignFile(archivo, 'configuracion.dat');
  Reset(archivo);
  Read(archivo, registro);
  CloseFile(archivo);

  PQConnection1.HostName := registro.host;
  PQConnection1.DatabaseName := registro.db;
  PQConnection1.Username := registro.user;
  PQConnection1.Password := registro.passwd;

  SQLQuery1.SQL.Text := 'select prd_cdg, prd_nombre, prd_descripcion, prd_precio1, prd_precio2, '
    + 'prd_precio3, prd_precio4 from producto';

  PQConnection1.Connected := true;
  SQLQuery1.Open;

  If SQLQuery2.Active = true then
    SQLQuery2.Close;

  SQLQuery2.SQL.Text := 'delete from producto';
  SQLQuery2.ExecSQL;

  while not SQLQuery1.EOF = true do
  begin
    prd_cdg := SQLQuery1.Fields[0].AsString;
    prd_nombre := SQLQuery1.Fields[1].AsString;
    prd_descripcion := SQLQuery1.Fields[2].AsString;
    prd_precio1 := SQLQuery1.Fields[3].AsString;
    prd_precio2 := SQLQuery1.Fields[4].AsString;
    prd_precio3 := SQLQuery1.Fields[5].AsString;
    prd_precio4 := SQLQuery1.Fields[6].AsString;

    QueryString := 'insert into producto(prd_cdg, prd_nombre, prd_descripcion, prd_precio1, '
      + 'prd_precio2, prd_precio3, prd_precio4) values(' + prd_cdg + ', ''' + prd_nombre + ''', '''
      + prd_descripcion + ''', ' + prd_precio1 + ', ' + prd_precio2 + ', ' +prd_precio3 + ', '
      + prd_precio4 + ')';

    SQLQuery2.SQL.Text := QueryString;
    SQLQuery2.ExecSQL;

    SQLQuery1.Next;
  end;

  SQLQuery1.Close;

  ordenar_grid();
end;

procedure TMainForm.MenuItem6Click(Sender: TObject);
begin
  nombre := InputBox('Busqueda por nombre', 'Ingrese parte del nombre del producto:', '');
  ordenar_grid();
end;

procedure TMainForm.MenuItem7Click(Sender: TObject);
begin
  nombre := '%';
  ordenar_grid();
end;

initialization
  ThousandSeparator := ',';
  DecimalSeparator := '.';

end.

